﻿Imports System.Data.SQLite
Imports System.Data
Imports Microsoft.VisualBasic.Devices
Imports System.Xml

Public Class SQLiteWallpaperService
  Inherits WallpaperService

  Public Sub New(ByVal connection As SQLiteConnection)
    MyBase.New(connection)
  End Sub

  Public Overrides Sub IncrementWallpaperSet(ByRef value As Wallpaper)
    Dim cmd As SQLiteCommand = _connection.CreateCommand()
    Dim sqls As String

    'sqls = "UPDATE skins SET s_set = s_set + 1 WHERE s_filename = '" & value.Filename & "'"
    sqls = "UPDATE skins SET s_set = ifnull(s_set,0) + 1 WHERE s_filename = @filename"

    cmd.CommandText = sqls

    cmd.Parameters.Add(New SQLiteParameter("filename", DbType.String))
    cmd.Parameters("filename").Value = value.Filename

    cmd.ExecuteNonQuery()

    cmd.Dispose()

    value.Gesetzt += 1
  End Sub

  Public Overrides ReadOnly Property CheckOrgaIDexists(ByVal orga_id As Integer, ByVal resolution As String) As Boolean
    Get
      Dim cmd As SQLiteCommand = _connection.CreateCommand()
      Dim da As SQLiteDataAdapter
      Dim ds As New DataSet
      Dim sqls As String

      sqls = "SELECT case count(*) when 0 then 'false' else 'true' end exist "
      sqls &= vbNewLine & " FROM skins "
      sqls &= vbNewLine & " WHERE s_organr = @organr "
      sqls &= vbNewLine & "   AND s_resolution = @resolution "

      cmd.CommandText = sqls

      cmd.Parameters.Add(New SQLiteParameter("organr", DbType.Int32))
      cmd.Parameters("organr").Value = orga_id
      cmd.Parameters.Add(New SQLiteParameter("resolution", DbType.String))
      cmd.Parameters("resolution").Value = resolution

      da = New SQLiteDataAdapter(cmd)
      da.Fill(ds)

      If ds.Tables(0).Rows.Count > 0 Then
        Return Boolean.Parse(ds.Tables(0).Rows(0).Item("exist"))
      Else
        cmd.Dispose()
        Return False
      End If

    End Get
  End Property

  Public Overrides ReadOnly Property GetWallpaperById(ByVal wallpaper_id As Integer) As Wallpaper
    Get
      Dim cmd As SQLiteCommand = _connection.CreateCommand()
      Dim sqls As String
      Dim result As ArrayList
      Dim w As New Wallpaper(Me)

      sqls = "SELECT * FROM skins WHERE s_id = @wall_id"

      cmd.CommandText = sqls

      cmd.Parameters.Add(New SQLiteParameter("wall_id", DbType.Int32))
      cmd.Parameters("wall_id").Value = wallpaper_id

      result = Utils.GetArrayListFromReader(cmd.ExecuteReader(), Utils.eColumnCaseDefinition.ToLower)

      For Each row As Hashtable In result
        w.Datum = row("s_date")
        w.Favorite = row("s_favorite")
        w.Filename = row("s_filename")
        w.FullName = row("s_true_name")
        w.Gesetzt = row("s_set")
        w.ID = row("s_id")
        w.Name = row("s_name")
        w.OrgaID = row("s_organr")
        w.Resolution = row("s_resolution")
      Next

      Return w

    End Get
  End Property



  Public Overrides ReadOnly Property IsFavoritePic(ByVal w As Wallpaper) As Boolean
    Get
      Dim cmd As SQLiteCommand = _connection.CreateCommand()
      Dim sqls As String
      Dim da As SQLiteDataAdapter
      Dim ds As New DataSet

      sqls = "SELECT case s_favorite when 'Y' then 'true' else 'false' end isfavpic FROM skins WHERE s_id = @id"

      cmd.CommandText = sqls

      cmd.Parameters.Add(New SQLiteParameter("id", DbType.Int32))
      cmd.Parameters("id").Value = w.ID

      da = New SQLiteDataAdapter(cmd)
      da.Fill(ds)

      Return Boolean.Parse(ds.Tables(0).Rows(0).Item("isfavpic"))
    End Get
  End Property

  Public Overrides ReadOnly Property IsFavoriteModel(ByVal w As Wallpaper) As Boolean
    Get
      Dim cmd As SQLiteCommand = _connection.CreateCommand()
      Dim sqls As String
      Dim da As SQLiteDataAdapter
      Dim ds As New DataSet

      sqls = "SELECT case count(*) when 0 then 'false' else 'true' end isfavmod FROM skins_favorites WHERE s_name = @sname"

      cmd.CommandText = sqls

      cmd.Parameters.Add(New SQLiteParameter("sname", DbType.String))
      cmd.Parameters("sname").Value = w.Name

      da = New SQLiteDataAdapter(cmd)
      da.Fill(ds)

      Return Boolean.Parse(ds.Tables(0).Rows(0).Item("isfavmod"))
    End Get
  End Property

  Public Overrides Sub SetFavoriteModel(ByRef w As Wallpaper)
    Dim cmd As SQLiteCommand = _connection.CreateCommand()
    Dim sqls As String
    sqls = "insert into skins_favorites(s_name) values(@sname)"
    cmd.CommandText = sqls

    cmd.Parameters.Add(New SQLiteParameter("sname", DbType.String))
    cmd.Parameters("sname").Value = w.Name

    cmd.ExecuteNonQuery()
  End Sub

  Public Overrides Sub SetFavoritePic(ByRef w As Wallpaper)
    Dim cmd As SQLiteCommand = _connection.CreateCommand()
    Dim sqls As String
    sqls = "update skins set s_favorite = 'Y' where s_id = @id"
    w.Favorite = True
    cmd.CommandText = sqls

    cmd.Parameters.Add(New SQLiteParameter("id", DbType.Int32))
    cmd.Parameters("id").Value = w.ID

    cmd.ExecuteNonQuery()
  End Sub

  Public Overrides Sub RemoveFavoriteModel(ByRef w As Wallpaper)
    Dim cmd As SQLiteCommand = _connection.CreateCommand()
    Dim sqls As String
    sqls = "delete from skins_favorites where s_name = @sname"
    cmd.CommandText = sqls

    cmd.Parameters.Add(New SQLiteParameter("sname", DbType.String))
    cmd.Parameters("sname").Value = w.Name

    cmd.ExecuteNonQuery()
  End Sub

  Public Overrides Sub RemoveFavoritePic(ByRef w As Wallpaper)
    Dim cmd As SQLiteCommand = _connection.CreateCommand()
    Dim sqls As String
    sqls = "update skins set s_favorite = null where s_id = @id"
    w.Favorite = False
    cmd.CommandText = sqls

    cmd.Parameters.Add(New SQLiteParameter("id", DbType.Int32))
    cmd.Parameters("id").Value = w.ID

    cmd.ExecuteNonQuery()
  End Sub

  Public Overrides ReadOnly Property GetNewDownloadableWallpaper() As DownloadabeWallpaper
    Get
      Dim cmd As SQLiteCommand = _connection.CreateCommand()
      Dim sqls As String
      Dim da As SQLiteDataAdapter
      Dim ds As New DataSet
      Dim dw As New DownloadabeWallpaper(Me)

      sqls = "insert into seq_skins(seq_value) values(null)"

      cmd.CommandText = sqls

      cmd.ExecuteNonQuery()

      cmd = _connection.CreateCommand()

      sqls = "select max(seq_value) newid from seq_skins "

      cmd.CommandText = sqls

      da = New SQLiteDataAdapter(cmd)
      da.Fill(ds)

      dw.ID = ds.Tables(0).Rows(0).Item("newid")

      cmd.Dispose()

      Return dw
    End Get
  End Property

  Public Overrides Sub InsertNewWallpaper(ByRef value As Wallpaper)
    Dim cmd As SQLiteCommand = _connection.CreateCommand()
    Dim sqls As String

    sqls = "INSERT INTO skins(s_filename, s_date, s_organr, s_name, s_true_name, s_resolution, s_id )"
    sqls &= vbNewLine & " VALUES( @filename, current_date, @organr, @sname, @fullname, @resolution, @id ) "

    cmd.CommandText = sqls

    cmd.Parameters.Add(New SQLiteParameter("filename", DbType.String))
    cmd.Parameters("filename").Value = value.Filename
    cmd.Parameters.Add(New SQLiteParameter("organr", DbType.Int32))
    cmd.Parameters("organr").Value = value.OrgaID
    cmd.Parameters.Add(New SQLiteParameter("sname", DbType.String))
    cmd.Parameters("sname").Value = value.Name
    cmd.Parameters.Add(New SQLiteParameter("resolution", DbType.String))
    cmd.Parameters("resolution").Value = value.Resolution
    cmd.Parameters.Add(New SQLiteParameter("id", DbType.Int32))
    cmd.Parameters("id").Value = value.ID
    cmd.Parameters.Add(New SQLiteParameter("fullname", DbType.String))
    cmd.Parameters("fullname").Value = Utils.InitCap(value.Name)

    cmd.ExecuteNonQuery()

    cmd.Dispose()

    value = GetWallpaperById(value.ID)

  End Sub

  Public Overrides ReadOnly Property GetMainList(Optional ByVal filter As String = "", Optional ByVal filtername As String = "Filter") As ListInfoWallpaper()
    Get
      Dim cmd As SQLiteCommand = _connection.CreateCommand()
      Dim sqls As String
      Dim liw() As ListInfoWallpaper
      Dim result As ArrayList

      cmd.CommandText = "drop view v_skins_overview"

      Try
        cmd.ExecuteNonQuery()
      Catch ex As Exception
        MsgBox(ex.Message)
      End Try

      cmd = _connection.CreateCommand()

      sqls = "create view v_skins_overview as " & vbNewLine
      If filter <> "" Then
        sqls &= vbNewLine & "select -1 id, '" & filtername & "' main_key, s_id, s_date, s_true_name, s_filename, s_resolution, s_true_name || ' (' || s_filename || ')' display, 'false' namesort  "
        sqls &= vbNewLine & " from skins "
        sqls &= vbNewLine & " where s_id is not null "
        sqls &= vbNewLine & " and s_filename is not null"
        sqls &= vbNewLine & " and s_filename like '%' || lower('" & filter & "') || '%' "
        sqls &= vbNewLine & " union"
      End If
      sqls &= vbNewLine & _config.ÜbersichtSQLSQLite

      cmd.CommandText = sqls

      cmd.Parameters.Add(New SQLiteParameter("resolution", DbType.String))
      cmd.Parameters("resolution").Value = _config.Resolution

      cmd.ExecuteNonQuery()

      sqls = " select * from v_skins_overview"
      sqls &= vbNewLine & " where s_resolution like @resolution "
      sqls &= vbNewLine & " order by id, s_true_name, s_date"

      cmd.CommandText = sqls

      cmd.Parameters.Add(New SQLiteParameter("resolution", DbType.String))
      cmd.Parameters("resolution").Value = _config.Resolution

      result = Utils.GetArrayListFromReader(cmd.ExecuteReader(), Utils.eColumnCaseDefinition.ToLower)

      If result.Count > 0 Then
        ReDim liw(result.Count - 1)
        Dim i As Integer = 0
        For Each row As Hashtable In result
          liw(i) = New ListInfoWallpaper
          With liw(i)
            .SortName = row("main_key")
            .SortNumber = row("id")
            .ID = row("s_id")
            .Datum = row("s_date")
            .FullName = row("s_true_name")
            .FileName = row("s_filename")
            .Display = row("display")
            .NameSort = Boolean.Parse(row("namesort"))
          End With
          i += 1
        Next
      Else
        ReDim liw(-1)
      End If
      Return liw
    End Get
  End Property

  Public Overrides Sub DeleteWallpaper(ByVal w As Wallpaper, ByVal filesystem As Boolean)
    Dim cmd As SQLiteCommand = _connection.CreateCommand()
    Dim sqls As String

    sqls = "DELETE FROM skins WHERE s_filename = @sfilename"

    cmd.CommandText = sqls

    cmd.Parameters.Add(New SQLiteParameter("sfilename", DbType.String))
    cmd.Parameters("sfilename").Value = w.Filename

    cmd.ExecuteNonQuery()

    MyBase.DeleteFromFilesystem(_config.SkinsPath & w.Filename)

  End Sub

  Public Overrides Sub UpdateWallpaper(ByVal w As Wallpaper)
    ' TODO: UpdateWallpaper ins SQLite Service
  End Sub

  Public Overrides Function GetWallpaperByName(ByVal name As String) As List(of Wallpaper)
    Dim cmd As SQLiteCommand = _connection.CreateCommand()
    Dim low As New List(Of wallpaper)
    Dim sqls As String
    Dim da As SQLiteDataAdapter
    Dim ds As New DataSet

    sqls = "select s_id from skins where s_name = :sname and s_resolution like :sresolution"

    cmd.CommandText = sqls

    cmd.Parameters.Add(New SQLiteParameter("sname", DbType.String))
    cmd.Parameters("sname").Value = name
    cmd.Parameters.Add(New SQLiteParameter("sresolution", DbType.String))
    cmd.Parameters("sresolution").Value = _config.Resolution

    da = New SQLiteDataAdapter(cmd)
    da.Fill(ds)

    For i As Integer = 0 To ds.Tables(0).Rows.Count - 1 Step 1
      low.Add(GetWallpaperById(ds.Tables(0).Rows(i).Item("s_id")))
    Next

    Return low
  End Function

  Public Overrides ReadOnly Property GetListOfWallpaper(ByVal ids As list(of Integer)) As System.Collections.Generic.List(Of Wallpaper)
    Get
      Return New List(Of Wallpaper)
    End Get
  End Property
  
  Public Overrides ReadOnly Property GetModelDetails() As List(Of ModelDetail)
  	get
  		Throw New NotImplementedException("Diese Funktion ist hier nicht implementiert.")
  	end get
  End Property

End Class
